const pool = require('../config/database');

class Car {
  // 创建车辆
  static async create(carData) {
    const {
      title, subtitle, brand, model, reg_date, distance, gearbox,
      emission, displacement, color, price, original_price,
      description, location, seller_id
    } = carData;

    const [result] = await pool.execute(
      `INSERT INTO cars 
       (title, subtitle, brand, model, reg_date, distance, gearbox, emission, displacement, 
        color, price, original_price, description, location, seller_id) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [title, subtitle, brand, model, reg_date, distance, gearbox, emission, displacement,
       color, price, original_price, description, location, seller_id]
    );

    return result.insertId;
  }


static async findAll(filters = {}, page = 1, limit = 10) {
  try {
    let query = `
      SELECT c.*, u.name as seller_name, u.avatar as seller_avatar,
             (SELECT image_url FROM car_images WHERE car_id = c.id AND is_main = TRUE LIMIT 1) as main_image
      FROM cars c
      LEFT JOIN users u ON c.seller_id = u.id
      WHERE c.status = 'approved'
    `;
    const params = [];

    // 构建筛选条件
    if (filters.brand && filters.brand !== '') {
      query += ' AND c.brand = ?';
      params.push(filters.brand);
    }

    if (filters.keyword && filters.keyword !== '') {
      query += ' AND (c.title LIKE ? OR c.description LIKE ?)';
      const keyword = `%${filters.keyword}%`;
      params.push(keyword, keyword);
    }

    if (filters.priceRange && filters.priceRange !== '') {
      const [minPrice, maxPrice] = filters.priceRange.split('-').map(Number);
      if (!isNaN(minPrice) && !isNaN(maxPrice)) {
        query += ' AND c.price BETWEEN ? AND ?';
        params.push(minPrice, maxPrice);
      } else if (!isNaN(minPrice)) {
        query += ' AND c.price >= ?';
        params.push(minPrice);
      }
    }

    // 添加车龄筛选
    if (filters.age && filters.age !== '') {
      const currentYear = new Date().getFullYear();
      if (filters.age === '1') {
        // 1年以内
        query += ' AND YEAR(c.reg_date) = ?';
        params.push(currentYear - 1);
      } else if (filters.age === '1-3') {
        // 1-3年
        query += ' AND YEAR(c.reg_date) BETWEEN ? AND ?';
        params.push(currentYear - 3, currentYear - 1);
      } else if (filters.age === '3-5') {
        // 3-5年
        query += ' AND YEAR(c.reg_date) BETWEEN ? AND ?';
        params.push(currentYear - 5, currentYear - 3);
      } else if (filters.age === '5') {
        // 5年以上
        query += ' AND YEAR(c.reg_date) <= ?';
        params.push(currentYear - 5);
      }
    }

    // 添加里程筛选
    if (filters.distance && filters.distance !== '') {
      const [minDist, maxDist] = filters.distance.split('-').map(Number);
      if (!isNaN(minDist) && !isNaN(maxDist)) {
        query += ' AND c.distance BETWEEN ? AND ?';
        params.push(minDist, maxDist);
      } else if (!isNaN(minDist)) {
        query += ' AND c.distance >= ?';
        params.push(minDist);
      }
    }

    console.log('构建的SQL查询:', query);
    console.log('查询参数:', params);

    // 排序
    let orderBy = 'c.created_at DESC';
    if (filters.sort && filters.sort !== 'default') {
      switch (filters.sort) {
        case 'price_asc':
          orderBy = 'c.price ASC';
          break;
        case 'price_desc':
          orderBy = 'c.price DESC';
          break;
        case 'distance_asc':
          orderBy = 'c.distance ASC';
          break;
        case 'distance_desc':
          orderBy = 'c.distance DESC';
          break;
      }
    }
    query += ` ORDER BY ${orderBy}`;

    // 分页
    const pageNum = parseInt(page) || 1;
    const limitNum = parseInt(limit) || 10;
    const offset = (pageNum - 1) * limitNum;
    
    query += ` LIMIT ${limitNum} OFFSET ${offset}`;

    // 执行查询
    const [rows] = params.length > 0 
      ? await pool.execute(query, params)
      : await pool.execute(query);

    console.log('查询结果数量:', rows.length);

    // 获取总数
    let countQuery = `
      SELECT COUNT(*) as total 
      FROM cars c 
      WHERE c.status = 'approved'
    `;
    const countParams = [];

    // 同样的筛选条件...
    if (filters.brand && filters.brand !== '') {
      countQuery += ' AND c.brand = ?';
      countParams.push(filters.brand);
    }

    if (filters.keyword && filters.keyword !== '') {
      countQuery += ' AND (c.title LIKE ? OR c.description LIKE ?)';
      const keyword = `%${filters.keyword}%`;
      countParams.push(keyword, keyword);
    }

    // ... 其他筛选条件

    const [countRows] = countParams.length > 0
      ? await pool.execute(countQuery, countParams)
      : await pool.execute(countQuery);
      
    const total = countRows[0].total;

    return {
      cars: rows,
      total,
      page: pageNum,
      limit: limitNum,
      totalPages: Math.ceil(total / limitNum)
    };
  } catch (error) {
    console.error('findAll 错误详情:', error);
    throw error;
  }
}

  // 根据ID获取车辆详情
  static async findById(id) {
    try {
      const [rows] = await pool.execute(
        `SELECT c.*, u.name as seller_name, u.avatar as seller_avatar
         FROM cars c
         LEFT JOIN users u ON c.seller_id = u.id
         WHERE c.id = ? AND c.status = 'approved'`,
        [id]
      );

      if (rows.length === 0) {
        return null;
      }

      const car = rows[0];

      // 获取车辆图片
      const [images] = await pool.execute(
        'SELECT * FROM car_images WHERE car_id = ? ORDER BY is_main DESC',
        [id]
      );
      car.images = images;

      // 获取检测报告
      const [inspections] = await pool.execute(
        'SELECT * FROM inspections WHERE car_id = ?',
        [id]
      );
      car.inspections = inspections;

      return car;
    } catch (error) {
      console.error('findById 错误:', error);
      throw error;
    }
  }

  // 更新车辆信息
  static async update(id, updateData) {
    const {
      title, subtitle, brand, model, reg_date, distance, gearbox,
      emission, displacement, color, price, original_price,
      description, location
    } = updateData;

    const [result] = await pool.execute(
      `UPDATE cars SET 
       title = ?, subtitle = ?, brand = ?, model = ?, reg_date = ?, distance = ?, gearbox = ?,
       emission = ?, displacement = ?, color = ?, price = ?, original_price = ?,
       description = ?, location = ?
       WHERE id = ?`,
      [title, subtitle, brand, model, reg_date, distance, gearbox, emission, displacement,
       color, price, original_price, description, location, id]
    );

    return result.affectedRows > 0;
  }

  // 删除车辆
  static async delete(id) {
    const [result] = await pool.execute('DELETE FROM cars WHERE id = ?', [id]);
    return result.affectedRows > 0;
  }

  // 获取用户发布的车辆
  static async findBySeller(sellerId) {
    const [rows] = await pool.execute(
      `SELECT c.*, 
              (SELECT image_url FROM car_images WHERE car_id = c.id AND is_main = TRUE LIMIT 1) as main_image
       FROM cars c 
       WHERE c.seller_id = ? 
       ORDER BY c.created_at DESC`,
      [sellerId]
    );

    return rows;
  }
}

module.exports = Car;